package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IQuerySalaryDataDao;
import com.boss.salary.entity.QuerySalaryData;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class QuerySalaryDataDaoImpl  extends BaseDaoImpl implements IQuerySalaryDataDao {


    /**
     * 获取工资支付数据 分页查询
     * @param paginationDTO
     * @param queryParam
     * @return
     */
    @Override
    public List<QuerySalaryData> getPayrollDataPage(PaginationDTO paginationDTO, Map<String, Object> queryParam,List<String> childrenIds) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select @i :=@i + 1 NO,id, agency_code, agency_name , MAX(IF(state = '3', realMoney, 0)) registerSalary,")
                .append("MAX(IF(state = '1', realMoney, 0)) quitSalary,SUM(realMoney) totalSalary , salary_month ");
        /** 单位基础信息表 **/
        sql.append(" from (select  av.id, ai.agency_code, ai.agency_name, pi.state ,")
                .append(" SUM(s1) realMoney , sh.salary_month from ")
                /***/
                .append(TableName.AGENCY_VERIFY).append(" as av ")
                /***/
                .append(" join  ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" on av.agency_id = ai.id")
                /** 单位基础信息表 **/
                .append("  join ").append(TableName.PERSON_HISTORY).append(" as ph")
                .append(" on ai.id = ph.agency_id")
                /** 机构上报审核状态表 **/
                .append("  join ").append(TableName.PERSON_INFO).append(" as pi")
                .append(" on pi.id = ph.person_id ")

                .append("  join ").append(TableName.SALARY_HISTORY).append(" as sh")
                .append(" on pi.id = sh.person_id ");
        sql.append(" where av.is_pay_data_created = 0")
                .append(" AND av.is_month_end = 0 and av.is_deleted=0 and ph.is_stop_salary = 0")
                .append(" and ").append(SysUtils.inSql(" ai.id ",childrenIds));
        if(!StringUtils.isEmpty(queryParam) ) {
            if(!StringUtils.isEmpty(queryParam.get("choiceOfTime"))){
                String id = queryParam.get("choiceOfTime").toString();
                sql.append(" and  sh.salary_month = ? ");
                paramList.add(id);

            }else{
                sql.append(" and  sh.salary_month=(select max(salary_month) from salary_history)");
            }
        }
        //查询未月结的 未被删除的
        sql.append(" group by av.id, ai.agency_code, ai.agency_name, pi.state, sh.salary_month order by agency_code ) t, (select @i := 0) n");
        sql.append(" group by id, agency_code, agency_name, salary_month");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(QuerySalaryData.class), params);
    }
    /**
     *获取工资支付数据 查询分页total
     * @param queryParam
     * @return
     */    @Override
    public int getPayrollDataTotal(Map<String, Object> queryParam,List<String> childrenIds) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from (select @i :=@i + 1 NO,id, agency_code, agency_name , MAX(IF(state = '3', realMoney, 0)) registerSalary,")
                .append("MAX(IF(state = '1', realMoney, 0)) quitSalary,SUM(realMoney) totalSalary  ");
        /** 单位基础信息表 **/
        sql.append(" from (select  av.id, ai.agency_code, ai.agency_name, pi.state ,")
                .append(" SUM(s1) realMoney from  ")
                /***/
                .append(TableName.AGENCY_VERIFY).append(" as av ")
                /***/
                .append(" join  ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" on av.agency_id = ai.id ")

                /** 单位基础信息表 **/
                .append("  join ").append(TableName.PERSON_HISTORY).append(" as ph")
                .append(" on ai.id = ph.agency_id")
                /** 机构上报审核状态表 **/
                .append("  join ").append(TableName.PERSON_INFO).append(" as pi")
                .append(" on pi.id = ph.person_id ")

                .append("  join ").append(TableName.SALARY_HISTORY).append(" as sh")
                .append(" on pi.id = sh.person_id ");
        sql.append(" where av.is_pay_data_created = 0")
                .append(" AND av.is_month_end = 0 and av.is_deleted=0 and ph.is_stop_salary = 0")
                .append(" and ").append(SysUtils.inSql(" ai.id ",childrenIds));
        if(!StringUtils.isEmpty(queryParam) ) {

            if(!StringUtils.isEmpty(queryParam.get("choiceOfTime"))){
                String id = queryParam.get("choiceOfTime").toString();
                sql.append(" and  sh.salary_month = ? ");
                paramList.add(id);
            }else{
                sql.append(" and  sh.salary_month=(select max(salary_month) from salary_history)");
            }
        }
        //查询未月结的 未被删除的
        sql.append(" group by av.id, ai.agency_code, ai.agency_name, pi.state order by agency_code ) t, (select @i := 0) n");
        sql.append(" group by id, agency_code, agency_name) as t");
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }
    /**
     * 查询工资支付数据 合计值
     * @param queryParam
     * @return
     */
    @Override
    public QuerySalaryData queryTotalWages( Map<String, Object> queryParam,List<String> childrenIds) {
        List<Object> sqlParams = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select sum(registerSalary) registerSalary, sum(quitSalary) quitSalary, sum(totalSalary) totalSalary from ")
                .append(" (select @i :=@i + 1 NO,id, agency_code, agency_name , MAX(IF(state = '3', realMoney, 0)) registerSalary,")
                .append("MAX(IF(state = '1', realMoney, 0)) quitSalary,SUM(realMoney) totalSalary");
        /** 单位基础信息表 **/
        sql.append(" from (select  av.id, ai.agency_code, ai.agency_name, pi.state ,")
                .append(" SUM(s1) realMoney from ")
                /***/
                .append(TableName.AGENCY_VERIFY).append(" as av ")
                /***/
                .append(" join  ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" on av.agency_id = ai.id")

                /** 单位基础信息表 **/
                .append("  join ").append(TableName.PERSON_HISTORY).append(" as ph")
                .append(" on ai.id = ph.agency_id")
                /** 机构上报审核状态表 **/
                .append("  join ").append(TableName.PERSON_INFO).append(" as pi")
                .append(" on pi.id = ph.person_id ")

                .append("  join ").append(TableName.SALARY_HISTORY).append(" as sh")
                .append(" on pi.id = sh.person_id ");
        sql.append(" where av.is_pay_data_created = 0")
                .append(" AND av.is_month_end = 0 and av.is_deleted=0 and ph.is_stop_salary = 0")
                .append(" and ").append(SysUtils.inSql(" ai.id ",childrenIds));

        if(!StringUtils.isEmpty(queryParam) ) {

            if(!StringUtils.isEmpty(queryParam.get("choiceOfTime"))){
                String id = queryParam.get("choiceOfTime").toString();
                sql.append(" and  sh.salary_month = ? ");
                sqlParams.add(id);
            }else{
                sql.append(" and  sh.salary_month=(SELECT MAX(salary_month) FROM salary_history)");
            }
        }
        //查询未月结的 未被删除的
        sql.append(" group by av.id, ai.agency_code, ai.agency_name, pi.state order by agency_code ) t, (select @i := 0) n");
        sql.append(" group by id, agency_code, agency_name) s");
        return this.getjdbcTemplate().queryForObject(sql.toString(),new BeanPropertyRowMapper<>(QuerySalaryData.class), sqlParams.toArray());
    }

    @Override
    public List<QuerySalaryData> getPayrollData(Map<String, Object> queryParam,List<String> childrenIds) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select @i :=@i + 1 NO,id, agency_code, agency_name , MAX(IF(state = '3', realMoney, 0)) registerSalary,")
                .append("MAX(IF(state = '1', realMoney, 0)) quitSalary,SUM(realMoney) totalSalary , salary_month ");
        /** 单位基础信息表 **/
        sql.append(" from (select  av.id, ai.agency_code, ai.agency_name, pi.state ,")
                .append(" SUM(s1) realMoney , sh.salary_month from ")
                /***/
                .append(TableName.AGENCY_VERIFY).append(" as av ")
                /***/
                .append(" join  ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" on av.agency_id = ai.id")
                /** 单位基础信息表 **/
                .append("  join ").append(TableName.PERSON_HISTORY).append(" as ph")
                .append(" on ai.id = ph.agency_id")
                /** 机构上报审核状态表 **/
                .append("  join ").append(TableName.PERSON_INFO).append(" as pi")
                .append(" on pi.id = ph.person_id ")

                .append("  join ").append(TableName.SALARY_HISTORY).append(" as sh")
                .append(" on pi.id = sh.person_id ");
        sql.append(" where av.is_pay_data_created = 0")
                .append(" AND av.is_month_end = 0 and av.is_deleted=0 and ph.is_stop_salary = 0")
                .append(" and ").append(SysUtils.inSql(" ai.id ",childrenIds));

        if(!StringUtils.isEmpty(queryParam) ) {
            if(!StringUtils.isEmpty(queryParam.get("choiceOfTime"))){
                String id = queryParam.get("choiceOfTime").toString();
                sql.append(" and  sh.salary_month = ? ");
                paramList.add(id);
            }else{
                sql.append(" and  sh.salary_month=(select max(salary_month) from salary_history)");
            }
        }
        //查询未月结的 未被删除的
        sql.append(" group by av.id, ai.agency_code, ai.agency_name, pi.state, sh.salary_month order by agency_code ) t, (select @i := 0) n");
        sql.append(" group by id, agency_code, agency_name, salary_month");

        //请求参数初始化

        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(QuerySalaryData.class), paramList.toArray());
    }
}
